package com.xls.base.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xls.base.model.Student;
import com.xls.base.vo.StudentHousingVo;
import com.xls.base.vo.StudentVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author xls
 * @since 2021-03-23
 */
public interface StudentMapper extends BaseMapper<Student> {

    @Select("SELECT\n" +
            "\tid,\n" +
            "\tsno,\n" +
            "\tname,\n" +
            "\t( CASE gender WHEN 1 THEN '男' ELSE '女' END ) AS gender,\n" +
            "\tphone,\n" +
            "\t( SELECT name FROM department WHERE department = id ) AS department,\n" +
            "\t( SELECT name FROM department WHERE major = id ) AS major \n" +
            "FROM\n" +
            "\tstudent" +
            "\t${ew.customSqlSegment} ORDER BY id")
    Page<StudentVo> getStudentPage(Page<StudentVo> page, @Param(Constants.WRAPPER) QueryWrapper<Student> wrapper);

    @Select("SELECT\n" +
            "\tid,\n" +
            "\tsno,\n" +
            "\tname,\n" +
            "\t( CASE gender WHEN 1 THEN '男' ELSE '女' END ) AS gender,\n" +
            "\tphone,\n" +
            "\t( SELECT name FROM department WHERE department = id ) AS department,\n" +
            "\t( SELECT name FROM department WHERE major = id ) AS major,\n" +
            "\t(SELECT gmt_create FROM housing WHERE student_id = s.id ORDER BY gmt_create DESC LIMIT 1) AS housingTime \n" +
            "FROM\n" +
            "\tstudent s \n" +
            "WHERE\n" +
            "\troom_id = #{roomId}")
    List<StudentVo> getStudentList(String roomId);

    @Select("SELECT\n" +
            "\ts.name,\n" +
            "\t( SELECT name FROM department WHERE department = id ) AS department,\n" +
            "\t( SELECT name FROM department WHERE major = id ) AS major,\n" +
            "\tCOALESCE(h.hobby,'') AS hobby,\n" +
            "\th.habit,\n" +
            "\th.plan,\n" +
            "\th.gmt_create AS housingTime \n" +
            "FROM\n" +
            "\tstudent s\n" +
            "\tLEFT JOIN housing h ON s.id = h.student_id \n" +
            "\tAND h.id = ( SELECT id FROM housing WHERE student_id = s.id AND type = 1 AND is_deleted = 0 ORDER BY gmt_create DESC LIMIT 1 ) \n" +
            "WHERE\n" +
            "\ts.room_id = #{roomId}")
    List<StudentHousingVo> getStudentHousingVoList(String roomId);
}
